---
title: "MySQL TSQL"
date: 2020-10-14
categories:
- mysql
tags:
---

<div id="content">
<p>
声明变量
</p>
<p>
set @row_number = 0;
</p>
<p>
declare 只能在存储过程中使用:
declare name varchar(50);
</p>
<p>
存储过程中的declare必须在最前面几行，否则报错
</p>
<p>
存储过程中 select from informat_schema返回null，原因未知
</p>
<div class="org-src-container">
<pre class="src src-sql"><span style="font-weight: bold;">drop</span> <span style="font-weight: bold;">table</span> <span style="font-weight: bold;">if</span> <span style="font-weight: bold;">exists</span> temp_update_time_zone;
<span style="font-weight: bold;">create</span> <span style="font-weight: bold;">table</span> <span style="font-weight: bold;">temp_update_time_zone</span>
    <span style="font-weight: bold;">select</span> <span style="font-weight: bold;">table_name</span>, <span style="font-weight: bold;">column_name</span>, ORDINAL_POSITION <span style="font-weight: bold;">from</span> information_schema.`COLUMNS`
    <span style="font-weight: bold;">where</span> table_schema=<span style="font-style: italic;">'test'</span> <span style="font-weight: bold;">and</span> data_type=<span style="font-style: italic;">'datetime'</span>;

# <span style="font-weight: bold;">DROP</span> <span style="font-weight: bold;">procedure</span> IF <span style="font-weight: bold;">EXISTS</span> `update_time_zone`;
DELIMITER $$
USE `test`$$
<span style="font-weight: bold;">CREATE</span> <span style="font-weight: bold;">PROCEDURE</span> `update_time_zone` ()
<span style="font-weight: bold;">BEGIN</span>
<span style="font-weight: bold;">declare</span> <span style="font-weight: bold;">table_name</span> <span style="font-weight: bold; text-decoration: underline;">varchar</span>(255);
<span style="font-weight: bold;">declare</span> <span style="font-weight: bold;">column_name</span> <span style="font-weight: bold; text-decoration: underline;">varchar</span>(255);
<span style="font-weight: bold;">declare</span> column_index <span style="font-weight: bold; text-decoration: underline;">int</span>;
<span style="font-weight: bold;">declare</span> done Bool <span style="font-weight: bold;">default</span> <span style="font-weight: bold;">False</span>;

<span style="font-weight: bold;">declare</span> s_list <span style="font-weight: bold;">cursor</span> <span style="font-weight: bold;">for</span> <span style="font-weight: bold;">select</span> * <span style="font-weight: bold;">from</span> temp_update_time_zone;
<span style="font-weight: bold;">declare</span> <span style="font-weight: bold;">continue</span> handler <span style="font-weight: bold;">for</span> <span style="font-weight: bold;">not</span> <span style="font-weight: bold;">found</span> <span style="font-weight: bold;">set</span> done=<span style="font-weight: bold;">True</span>;

<span style="font-weight: bold;">drop</span> <span style="font-weight: bold;">temporary</span> <span style="font-weight: bold;">table</span> <span style="font-weight: bold;">if</span> <span style="font-weight: bold;">exists</span> update_log;
<span style="font-weight: bold;">create</span> <span style="font-weight: bold;">TEMPORARY</span> <span style="font-weight: bold;">table</span> <span style="font-weight: bold;">update_log</span>(
    `<span style="font-weight: bold;">sql</span>` <span style="font-weight: bold; text-decoration: underline;">varchar</span>(500)
);
<span style="font-weight: bold;">open</span> s_list;
myloop: loop
        <span style="font-weight: bold;">fetch</span> s_list <span style="font-weight: bold;">into</span> <span style="font-weight: bold;">table_name</span>, <span style="font-weight: bold;">column_name</span>, column_index;
    if done <span style="font-weight: bold;">then</span>
        leave myloop;
        <span style="font-weight: bold;">end</span> if;

    <span style="font-weight: bold;">set</span> @sql_update = concat(<span style="font-style: italic;">'update '</span>, <span style="font-weight: bold;">table_name</span>,<span style="font-style: italic;">' set '</span>, <span style="font-weight: bold;">column_name</span>, <span style="font-style: italic;">'=date_sub('</span> ,<span style="font-weight: bold;">column_name</span>, <span style="font-style: italic;">', INTERVAL 8 hour) where '</span>,<span style="font-weight: bold;">column_name</span>,<span style="font-style: italic;">' is not null'</span>);
    <span style="font-weight: bold;">PREPARE</span> stmt <span style="font-weight: bold;">FROM</span> @sql_update;
    <span style="font-weight: bold;">execute</span> stmt;
    <span style="font-weight: bold;">deallocate</span> <span style="font-weight: bold;">prepare</span> stmt;
    <span style="font-weight: bold;">insert</span> <span style="font-weight: bold;">into</span> update_log <span style="font-weight: bold;">values</span> (@sql_update);

<span style="font-weight: bold;">end</span> loop;
<span style="font-weight: bold;">close</span> s_list;


<span style="font-weight: bold;">select</span> * <span style="font-weight: bold;">from</span> update_log;
<span style="font-weight: bold;">END</span>$$

DELIMITER ;
</pre>
</div>
</div>
<div class="status" id="postamble">
<p class="date">Date: 2020-10-14</p>
<p class="author">Author: gdme1320</p>
<p class="validation"><a href="http://validator.w3.org/check?uri=referer">Validate</a></p>
</div>
